<?php
namespace Home\Controller;
use Think\Controller;
/**
**@desc 数据导入
*/

class StructureController extends CommonController {
	private $auth_status = 0;//默认：未认证
    private $user_email = null;//用户邮箱
    private $uid = null;
    private $auth_type = 1;
    private $user_name = '';
    private $cookie_name = "37degree_";
    private $upload_save_file_path = "";

    public function __construct(){
        //是否服务商用户，且登录状态
        parent::__construct();

        $this->upload_save_file_path = APP_PATH.'/../Data/';

		//判断用户是否服务商用户
        if($this->role==1){
            redirect('/Index/index');
        }
        
        $this->getAuthStatus();
        $this->auth_status = session('37degree_auth_status');
        
        $this->user_email = session('user_email');
        $this->uid = session('uid');
        $this->auth_type = session('auth_type');
        $this->user_name = session('user_name');

        $this->assign('user_email',$this->user_email);
        if(empty($this->uid)) redirect('/member/login.html?role=company');
    }
    
    //导入数据管理-列表
    public function index(){
        $type = I('get.type',1,'intval');

        $this->display();
    }

    //获取数据导入列表
    public function ajaxGetListData(){
    	if(empty($this->uid)) $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'未登录') );

    	//文本类型or数据库类型;页码
    	$page = I('post.page',1,'intval');
    	$pagesize = I('post.pagesize',20,'intval');
    	$type = I('post.type',1,'intval');

    	//获取数据，分页
    	$data = array();
    	$data['list'] = null;
    	$data['totalpage'] = 0;
    	$data['page'] = $page;
    	switch ($type){
    		case 2://文本
    			$fileModel = D('DataFile');
    			$list = $fileModel->getList($this->uid,$page, $pagesize);
    			$data['list'] = $list['list'];
    			$data['page'] = $page;
    			$data['totalpage'] = ceil($list['count']/$pagesize);
    			break;
    		case 1://数据库
    			$fileModel = D('DataDb');
    			$list = $fileModel->getList($this->uid,$page, $pagesize);
    			$data['list'] = $list['list'];
    			$data['page'] = $page;
    			$data['totalpage'] = ceil($list['count']/$pagesize);
    			break;
    		default:
    			//todo
    			break;
    	}

    	//返回数据
    	$this->ajaxReturn( array('status'=>200,'data'=>$data,'msg'=>'OK') );

    }

    //step1-第一步，选择
    public function importin(){
    	$this->display();
    }


    //step2，文件类型，信息导入页面
    public function importfile(){
    	$type = I('get.type','csv');
    	// $id = I('get.id',null,'intval');

    	if(in_array(strtolower($type),array('csv','json'))){
    		$this->assign('type',strtolower($type) );
    		
    		$this->display();	
    	}else{
    		redirect('/structure/importin.html');
    	}

    }

    //step2，文件类型，信息导入编辑页面
    public function editImportfile(){
    	$type = I('get.type','csv');
    	$id = I('get.id',null,'intval');
    	if(empty($id) || !is_numeric($id)) redirect('/Structure/importfile.html');

    	if(in_array(strtolower($type),array('csv','json'))){
    		$this->assign('type',strtolower($type) );
    		
    		//获取上传文件及填写信息
    		$fileModel = D('DataFile');
    		$info = $fileModel->getInfoWithId($id);
    		if(!empty($info)){
    			$info['import_condition'] = json_decode($info['import_condition'],true);
    		}
    		// echo "<pre>";print_r($info);die;

    		$this->assign('info',$info);
    		$this->display();	
    	}else{
    		redirect('/structure/importin.html');
    	}
    }

    //step3，文件中信息过滤，建立规则
    public function filefieldrule(){
    	$dataFile_id = I('get.id',null,'intval');

    	if($dataFile_id){
    		
    		$DataFileModel = D('DataFile');
    		$info = $DataFileModel->getInfoWithId($dataFile_id);
    		if(!empty($info)){
    			// echo "<pre>";print_r($info);
    			$file_conditin = json_decode($info['import_condition'],true);
    			// echo "<pre>";print_r($file_conditin);
    			$line_header = $file_conditin['line_header'];
    			$line_col_name = explode(",", $file_conditin['line_fields']);

    			//有列行头则读取文件列行头信息or读取表记录中用户输入数据（非压缩打包文件）
	    		if($line_header==0){//csv文件包含列行头，则读取
	    			$file = fopen($this->upload_save_file_path.$info['save_file_name'],'r'); 
	    			$data = fgetcsv($file);//只读取第一行
	    			// echo "<pre>";print_r($data);die;
	    			$line_title = $data;
	    		}else{
	    			$line_title = $line_col_name;
	    		}

                //已有字段信息
                $import_fields = json_decode($info['import_fields'],true);
                $db_fields = array();
                foreach ($import_fields as $k=>$v){
                    $name = $v['name'];
                    $type = $v['type'];
                    $desc = $v['desc'];
                    $db_fields[$name] = array('name'=>$name,'type'=>$type,'desc'=>$desc);
                }
                $this->assign('import_fields',$db_fields);

	    		// cookie($this->cookie_name."s_2",true);
	    		// echo "<pre>";print_r($line_title);die;
	    		$this->assign('fields',$line_title);
	    		$this->assign('id',$info['id']);  
	    		$this->display();
	    		
    		}else{
    			$this->redirect('/Structure/importfile.html?type=csv');
    		}
    		
    	}else{
    		$this->redirect('/Structure/importin.html');
    	} 
    }

    //step3，文件中信息过滤，建立规则,编辑页面
    public function editFilefieldrule(){

    }

    //step2,数据库导入
    //数据库类型信息，链接信息导入页面
    public function connectdb(){
    	if(IS_POST){
    		$data = I('post.',null);
            if(isset($data['ip']) && isset($data['account']) && isset($data['pwd']) && isset($data['dbname']))
    		if(!$this->uid) $this->ajaxReturn( array('status'=>1005,'data'=>null,'msg'=>'请重新登录') );

            //链接测试库，是否连通
            try{
                if(isset($data['port'])) $port = $data['port'];
                else $port = '3306';

                $test_connect = $this->testConnectDbWithMysql($data['ip'],$port ,$data['account'],$data['pwd'],$data['dbname']);

                if($test_connect===true){//数据库链接成功,且数据库存在
                    //数据入库
                    $dataModel = D('DataDb');
                    $sql_data['name'] = $data['ip'];
                    $sql_data['uid'] = $this->uid;
                    $sql_data['db_type'] = 'mysql';//目前只支持该类型
                    $sql_data['hostname'] = $data['ip'];
                    $sql_data['account'] = $data['account'];
                    $sql_data['password'] = $data['pwd'];
                    $sql_data['dbname'] = $data['dbname'];
                    $sql_data['port'] = $data['port'];
                    $sql_data['update_time'] = time();
                    if(isset($data['dbid']) && $data['dbid']){
                        $sql_data['id'] = $data['dbid'];
                        $result = $dataModel->updateOne($sql_data);
                    }else{
                        $sql_data['create_time'] = time();
                        $result = $dataModel->insertOne($sql_data);
                    }
                    

                    if($result){
                        $this->ajaxReturn( array('status'=>200,'data'=>$data,'msg'=>'链接成功') );
                    }else{
                        $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'录入数据失败') );
                    }
                    
                }else{
                    switch($test_connect){
                        case -1:
                            $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'链接信息不完整') );
                            break;
                        case -2:
                            $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'链接数据库失败') );
                            break;
                        case -3:
                            $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>$data['dbname']."不存在数据库") );
                            break;
                        default:
                            $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>"无法识别") );
                            break;
                    }
                }
            }catch(exception $e){
                $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>$e->getError()) );
            }
            
            //mysql_select_db

            //$sql   = 'SHOW COLUMNS FROM `'.$tableName.'`';
            //'SHOW TABLES FROM '.$dbName:'SHOW TABLES ';
    	}else{
    		$id = I('get.id',null,'intval');
	    	if($id){
	    		//获取数据
	    		$dbModel = D('DataDb');
	    		$info = $dbModel->getInfoWithId($id);

                // echo "<pre>";print_r($info);die;
	    		$this->assign('info',$info);
	    	}

	    	$this->display();
    	}
    }

    //对选择的数据库，获取所有表，选择表
    public function selectdbtable(){
    	if(IS_POST){
            if(!$this->uid) $this->ajaxReturn( array('status'=>1005,'data'=>null,'msg'=>'请重新登录'));
            //获取数据
            $dbid = I('post.id',null,'intval');
            $selected_table = I('post.selected_table',null);

            if(empty($dbid) || empty($selected_table)) $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'缺少必要参数'));
            //更新表数据
            $datadbmodel = D('DataDb');
            $data['id'] = $dbid;
            $data['import_condition'] = json_encode( array('selected_table'=>$selected_table) );
            $result = $datadbmodel->updateOne($data);
            if($result){
                $this->ajaxReturn( array('status'=>200,'data'=>null,'msg'=>'OK'));
            }else{
                $this->ajaxReturn( array('status'=>1002,'data'=>null,'msg'=>'操作失败'));
            }
        }else{
           //获取id
            $id = I('get.id',null,'intval');
            if(empty($id) || !is_numeric($id)) redirect('/Structure/connectdb.html');

            //获取数据库连接信息
            $dbModel = D('DataDb');
            $info = $dbModel->getInfoWithId($id);
            if(!empty($info)){
                //获取数据库表信息
                $tables = array();
                $tables = $this->getDbTables($info['hostname'],$info['port'],$info['account'],$info['password'],$info['dbname']);
                // echo "<pre>";print_r($tables);die;
                
                $this->assign('tables',$tables);
                //页面展示
                $this->assign('id',$id);
                $this->display();
            }else{
               redirect('/Structure/connectdb.html'); 
            } 
        }

    }

    //对数据库表字段信息，过滤，建立规则
    public function dbfieldrule(){
        $id = I('get.id',null,'intval');

        if($id){
            $DatadbModel = D('DataDb');
            $info = $DatadbModel->getInfoWithId($id);
            if(!empty($info)){
                // echo "<pre>";print_r($info);
                $file_conditin = json_decode($info['import_condition'],true);
                
                $import_fields = json_decode($info['import_fields'],true);
                $db_fields = array();
                foreach ($import_fields as $k=>$v){
                    $name = $v['name'];
                    $type = $v['type'];
                    $desc = $v['desc'];
                    $db_fields[$name] = array('name'=>$name,'type'=>$type,'desc'=>$desc);
                }

                if(!empty($file_conditin)) {
                    $selected_table = $file_conditin['selected_table'];

                    //获取表所有字段信息
                    $fields = $this->getFields($info['hostname'],$info['port'],$info['account'],$info['password'],$info['dbname'],$selected_table);
                    // echo "<pre>";print_r($fields);print_r($db_fields);die;
                    $this->assign('fields',$fields);
                    $this->assign('import_fields',$db_fields);
                }else{
                    redirect('/Structure/selectdbtable.html?id='.$id);
                }

                $this->assign('id',$info['id']);  
                $this->display();
                
            }else{
                $this->redirect('/Structure/connectdb.html');
            }
            
        }else{
            $this->redirect('/Structure/connectdb.html');
        }  

    }

    //对文件上传处理
    public function uploadFile(){
    	//获取上传文件
    	$upload = new \Think\Upload();// 实例化上传类
        // $upload->maxSize   =     524288 ;// 设置附件上传大小
        $upload->rootPath  =     $this->upload_save_file_path; // 设置附件上传根目录
        $upload->savePath  =     ''; // 设置附件上传（子）目录
        
        $upload->autoSub = true;//
        $upload->subName = array('date', 'Ymd');

        // 上传文件 
        $info   =   $upload->upload();
        if(!$info) {// 上传错误提示错误信息
            $this->ajaxReturn(array('status'=>1002,'data'=>null,'msg'=>$upload->getError()) );
        }else{// 上传成功
            $data['save_name'] = $this->rootPath.$info['file']['savepath'].$info['file']['savename'];
            $data['file_name'] = $_FILES['file']['name'];
            $this->ajaxReturn( array('status'=>200,'data'=>$data,'msg'=>'上传成功') );
        }

    }

    //step2，上传文件信息介绍（ajax提交数据）
    public function uploadfileinfo(){
    	//获取文件基本介绍信息
    	$data = I('post.');
    	
    	if(empty($data)){
    		$this->ajaxReturn(array('status'=>1002,'data'=>null,'msg'=>'数据有误') );
    	}

    	if(empty($data['file_name'])){
    		$this->ajaxReturn(array('status'=>1002,'data'=>null,'msg'=>'没有上传文件相关信息') );	
    	}
    	$save_file_name = $data['save_file_name'];
    	$file_name = $data['file_name'];

    	if(! $this->uid){
    		$this->ajaxReturn(array('status'=>1005,'data'=>null,'msg'=>'请重新登录') );
    	}

    	$sql_data = array();
    	$sql_data['name'] = $sql_data['source_file_name'] = $file_name;
    	$sql_data['uid'] = $this->uid;
    	$sql_data['save_file_name'] = $save_file_name;
    	$sql_data['file_type'] = $data['file_type'];

    	$sql_data['import_condition'] = json_encode($data);
    	$sql_data['create_time'] = $sql_data['update_time'] = time();

    	//信息入库
    	$DataFileModel = D('DataFile');
    	$result = $DataFileModel->insertOne($sql_data);

    	if($result){
    		// cookie($this->cookie_name.'upload_file_status',true);
    		// cookie($this->cookie_name."id",$result);

    		$this->ajaxReturn( array('data'=>$result,'status'=>200,'msg'=>'Ok') );
    	}else{
    		$this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'信息录入失败') );
    	}

    }

    //文件类型字段信息过滤，提交
    public function ajaxFilefield(){
    	//获取信息
    	$post = I('post.');
    	$data = $post['data'];
    	$id = $post['id'];
    	
    	//判断信息
    	if(empty($data)) $this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'数据不正确') );
    	// if(cookie($this->cookie_name."s_2")==true && cookie($this->cookie_name.'upload_file_status')==true){
    		// $dataFile_id = cookie($this->cookie_name."id");
    		// if($dataFile_id==$id){
    			//对数据处理
	    		$fields_data = array();
	    		foreach ($data as $k=>$v){
	    			$fields_data[] = array('name'=>$v[0],'type'=>$v[1],'desc'=>$v[2]);
	    		}
	    		
	    		$DataFileModel = M('DataFile');
	    		$sql_data['import_fields'] = json_encode($fields_data);
	    		$sql_data['update_time'] = time();
	    		$result = $DataFileModel->where('id='.$id)->save($sql_data);
	    		if($result){
	    			$this->ajaxReturn( array('data'=>null,'status'=>200,'msg'=>'Ok') );
	    		}else{
	    			$this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'数据录入失败') );
	    		}

    		// }else{
    		// 	$this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'数据不正确') );
    		// }
    		
    	// }else{
    	// 	$this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'error') );
    	// }

    }

    public function ajaxDbfield(){
        //获取信息
        $post = I('post.');
        $data = $post['data'];
        $id = $post['id'];
        
        //判断信息
        if(empty($data)) $this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'数据不正确') );
        //对数据处理
        $fields_data = array();
        foreach ($data as $k=>$v){
            $fields_data[] = array('name'=>$v[0],'type'=>$v[1],'desc'=>$v[2]);
        }
        
        $DataFileModel = M('DataDb');
        $sql_data['import_fields'] = json_encode($fields_data);
        $sql_data['update_time'] = time();
        // echo "<pre>";print_r($sql_data);die;
        $result = $DataFileModel->where('id='.$id)->save($sql_data);
        if($result){
            $this->ajaxReturn( array('data'=>null,'status'=>200,'msg'=>'Ok') );
        }else{
            $this->ajaxReturn( array('data'=>null,'status'=>1002,'msg'=>'数据录入失败') );
        }
    }

    //删除操作
    public function ajaxDelete(){
        $id = I('post.id',null,'intval');
        $type = I('post.type',null,'intval');
        if(!$id || !$type) $this->ajaxReturn( array('status'=>1003,'msg'=>'数据不正确','data'=>null) );

        if(!$this->uid) $this->ajaxReturn( array('status'=>1005,'msg'=>'请重新登录','data'=>null) );

        //库or文件
        switch($type){
            case 1://库
                $fileModel = D('DataDb');
                break;
            case 2://文件
                $fileModel = D('DataFile');
                break;
            default:
                $this->ajaxReturn( array('status'=>1003,'msg'=>'数据不正确','data'=>null) );
                break;
        }

        $result = $fileModel->deleteOne($id);
        if($result){
            $this->ajaxReturn( array('status'=>200,'msg'=>'OK','data'=>null) );
        }else{
            $this->ajaxReturn( array('status'=>1003,'msg'=>'操作失败','data'=>null) );
        }

    }   
     
    //字段重新编辑

    //条件重新编辑

    //测试连通数据库
    private function testConnectDbWithMysql($ip,$port='3306',$account,$pwd,$dbname){
        if(empty($ip) || empty($account) || empty($pwd) || empty($dbname)) return -1;//缺少必要链接信息
        if(filter_var($ip, FILTER_VALIDATE_IP)){//ip
            $con = mysql_connect($ip.":".$port,$account,$pwd);
        }else{
            $con = mysql_connect($ip,$account,$pwd);
        }
        
        if (!$con)
        {
          mysql_close($con);
          return -2;//无法链接数据库，链接失败
          // die('Could not connect: ' . mysql_error());
        }else{
            //查看该数据库是否存在
            $databases_array = array();
            $db_list = mysql_list_dbs($con);
            while ($row = mysql_fetch_object($db_list)) {
                array_push($databases_array, $row->Database);
            }
            mysql_close($con);
            
            if(!in_array($dbname,$databases_array)){
                return -3;//链接成功，但是数据库不存在
            }
            
            return true;
        }

        // mysql_close($con);
    }

    //根据数据库信息，返回所有表信息
    private function getDbTables($ip,$port='3306',$account,$pwd,$dbname){
        $tables = array();
       if(empty($ip) || empty($account) || empty($pwd) || empty($dbname)) return $tables;

        if(filter_var($ip, FILTER_VALIDATE_IP)){//ip
            $con = mysql_connect($ip.":".$port,$account,$pwd);
        }else{
            $con = mysql_connect($ip,$account,$pwd);
        } 
        if($con){
            //查看该数据库是否存在
            $databases_array = array();
            $db_list = mysql_list_dbs($con);
            while ($row = mysql_fetch_object($db_list)) {
                array_push($databases_array, $row->Database);
            }
            //获取表信息
            if(in_array($dbname,$databases_array)){
                $tb_list = mysql_list_tables($dbname);
                while ($row= mysql_fetch_row($tb_list)) {
                    array_push($tables,$row[0]);
                }
            }
            
        }
        return $tables;
    }

    //获取数据库表中字段信息
    private function getFields($ip,$port='3306',$account,$pwd,$dbname,$tablename){
        $fields = array();
       if(empty($ip) || empty($account) || empty($pwd) || empty($dbname)) return $fields;

        if(filter_var($ip, FILTER_VALIDATE_IP)){//ip
            $con = mysql_connect($ip.":".$port,$account,$pwd);
        }else{
            $con = mysql_connect($ip,$account,$pwd);
        } 
        if($con){
            //查看该数据库是否存在
            $databases_array = array();
            $db_list = mysql_list_dbs($con);
            while ($row = mysql_fetch_object($db_list)) {
                array_push($databases_array, $row->Database);
            }

            //获取表字段信息
            if(in_array($dbname,$databases_array)){
                mysql_select_db($dbname,$con);
                $query="select * from ".$tablename;
                $result=mysql_query($query);

                $fields_num = mysql_num_fields($result);
                for($i=0;$i<$fields_num;$i++){
                    array_push($fields, mysql_field_name($result,$i) );//逐个获取字段名
                }
            }
            
        }
        return $fields;
    }

    public function test(){
        $con = mysql_connect("www.phpmyadmin.com","dmp","dmpdev2015");
        if($con){
            // //获取服务器上数据库信息
            // $db_list = mysql_list_dbs($con);
            // while ($row = mysql_fetch_object($db_list)) {
            //          echo $row->Database . "<br>";
            // }

            // mysql_close($con);

            // echo "<pre>";print_r($result);die;
            // //获取数据库下所有表信息
            // $tb_list = mysql_list_tables("37degree_dataex");
            // while ($row= mysql_fetch_row($tb_list)) {
            //          echo $row[0]."<br>";
            // }

            // mysql_close($connection);
            //获取某库某表下所有字段名称
            mysql_select_db("37degree_dataex",$con) or die("选择数据库失败");
            $query="select * from dmp_data_file";

            $result=mysql_query($query) or die("查询用户失败");
            
            $fields_num = mysql_num_fields($result);
            for($i=0;$i<$fields_num;$i++){
                echo mysql_field_name($result,$i);                                              //输出第一个字段名
                echo "<br>";
            }                                          //输出第二个字段名

            mysql_close($connection);
        }else{
            die('Could not connect: ' . mysql_error());
        }
    }

}